import pandas as pd
import re
import json

def match_excel_v1(out_file_path):
    # 读取Excel文件
    file_path = r"C:\Users\64531\Desktop\ABsheet处理数据 - 原始.xlsx"
    asheet = pd.read_excel(file_path, sheet_name='A')
    bsheet = pd.read_excel(file_path, sheet_name='B')

    # 确保Bsheet中存在U列，并将其转换为字符串类型
    if 'match' not in bsheet.columns:
        bsheet['match'] = None
    bsheet['match'] = bsheet['match'].astype(str)

    # 在ASheet中添加isMatch列，并将其转换为布尔类型
    asheet['isMatch'] = False
    asheet['isMatch'] = asheet['isMatch'].astype(bool)

    # 遍历ASheet中的每一行
    for index_a, a_value in asheet.iloc[:, 0].items():
        # 遍历Bsheet中的P列
        for index_b, b_value in bsheet['BasicInfoJson'].items():
            # 检查a_value是否是b_value的子串
            if a_value in str(b_value):
                # 匹配成功，将ASheet中的值复制到Bsheet的U列
                bsheet.at[index_b, 'match'] = str(a_value)
                # 标记ASheet中的isMatch列为True
                asheet.at[index_a, 'isMatch'] = True
                break  # 找到一个匹配后跳出内层循环

    # 保存修改后的ASheet和Bsheet到新的Excel文件
    output_file_path = out_file_path
    with pd.ExcelWriter(output_file_path) as writer:
        asheet.to_excel(writer, sheet_name='A', index=False)
        bsheet.to_excel(writer, sheet_name='B', index=False)

    print("处理完成，结果已保存到", output_file_path)

def match_excel_v2(out_file_path):
    # 读取Excel文件
    file_path = r"C:\Users\64531\Desktop\ABsheet处理数据 - 原始.xlsx"
    asheet = pd.read_excel(file_path, sheet_name='A')
    bsheet = pd.read_excel(file_path, sheet_name='B')

    # 确保Bsheet中存在U列，并将其转换为字符串类型
    if 'match' not in bsheet.columns:
        bsheet['match'] = None
    bsheet['match'] = bsheet['match'].astype(str)

    # 在ASheet中添加isMatch列，并将其转换为布尔类型
    asheet['isMatch'] = False
    asheet['isMatch'] = asheet['isMatch'].astype(bool)

    # 记录匹配的详细信息
    matches = []

    # 遍历ASheet中的每一行
    for index_a, a_value in asheet.iloc[:, 0].items():
        matched = False
        # 遍历Bsheet中的P列
        for index_b, b_value in bsheet['BasicInfoJson'].items():
            # 检查a_value是否是b_value的子串
            if a_value in str(b_value):
                # 匹配成功，将ASheet中的值复制到Bsheet的U列
                bsheet.at[index_b, 'match'] = str(a_value)
                # 标记ASheet中的isMatch列为True
                asheet.at[index_a, 'isMatch'] = True
                matched = True
                matches.append((index_a, index_b, a_value, b_value))
                break  # 找到一个匹配后跳出内层循环
        if not matched:
            print(f"未匹配到: {a_value}")

    # 保存匹配的详细信息到CSV文件
    matches_df = pd.DataFrame(matches, columns=['ASheet_Index', 'Bsheet_Index', 'A_Value', 'B_Value'])
    matches_df.to_csv(r'C:\Users\64531\Desktop\matches.csv', index=False)

    # 保存修改后的ASheet和Bsheet到新的Excel文件
    output_file_path = out_file_path
    with pd.ExcelWriter(output_file_path) as writer:
        asheet.to_excel(writer, sheet_name='A', index=False)
        bsheet.to_excel(writer, sheet_name='B', index=False)

    print("处理完成，结果已保存到", output_file_path)

def match_excel_v3(out_file_path):


    # 读取Excel文件
    file_path = r"C:\Users\64531\Desktop\ABsheet处理数据 - 原始.xlsx"
    asheet = pd.read_excel(file_path, sheet_name='A')
    bsheet = pd.read_excel(file_path, sheet_name='B')

    # 确保Bsheet中存在U列，并将其转换为字符串类型
    if 'match' not in bsheet.columns:
        bsheet['match'] = None
    bsheet['match'] = bsheet['match'].astype(str)

    # 在ASheet中添加isMatch列，并将其转换为布尔类型
    asheet['isMatch'] = False
    asheet['isMatch'] = asheet['isMatch'].astype(bool)

    # 记录匹配的详细信息
    matches = []

    # 遍历ASheet中的每一行
    for index_a, a_value in asheet.iloc[:, 0].items():
        matched = False
        # 遍历Bsheet中的P列
        for index_b, b_value in bsheet['BasicInfoJson'].items():
            # 检查a_value是否是b_value的子串，并且b_value中包含'historyNames'
            if a_value in str(b_value) and 'historyNames' in str(b_value):
                # 匹配成功，将ASheet中的值复制到Bsheet的U列
                bsheet.at[index_b, 'match'] = str(a_value)
                # 标记ASheet中的isMatch列为True
                asheet.at[index_a, 'isMatch'] = True
                matched = True
                matches.append((index_a, index_b, a_value, b_value))
                break  # 找到一个匹配后跳出内层循环
        if not matched:
            print(f"未匹配到: {a_value}")

    # 保存匹配的详细信息到CSV文件
    matches_df = pd.DataFrame(matches, columns=['ASheet_Index', 'Bsheet_Index', 'A_Value', 'B_Value'])
    matches_df.to_csv(r'C:\Users\64531\Desktop\matches.csv', index=False)

    # 保存修改后的ASheet和Bsheet到新的Excel文件
    output_file_path = out_file_path
    with pd.ExcelWriter(output_file_path) as writer:
        asheet.to_excel(writer, sheet_name='A', index=False)
        bsheet.to_excel(writer, sheet_name='B', index=False)

    print("处理完成，结果已保存到", output_file_path)

def bing_search(input_file_path,out_file_path):


    # 读取Excel文件
    file_path = input_file_path  # 替换为你的Excel文件路径
    df = pd.read_excel(file_path)

    # 获取match列和name列的数据
    match_set = set(df['match'].dropna())
    name_set = set(df['name1'].dropna())

    # 找出match列中不在name列的数据
    df['123'] = df['match'].apply(lambda x: x if x not in name_set else None)

    # 找出name列中不在match列的数据
    df['321'] = df['name1'].apply(lambda x: x if x not in match_set else None)

    # 保存结果到新的Excel文件
    output_file_path = out_file_path  # 替换为你想要保存的文件路径
    df.to_excel(output_file_path, index=False)

    print("处理完成，结果已保存到:", output_file_path)




def get_excel_data():
    # 读取Excel文件中的sheet1
    input_file_path = r"C:\Users\64531\Desktop\主数据\02bi各个系统客商查询\02BI系统客商合并且去重.xlsx"
    output_file_path = r"C:\Users\64531\Desktop\主数据\02bi各个系统客商查询\02BI系统客商合并且去重且去除无效.xlsx"

    # 读取Excel文件中的sheet1
    df = pd.read_excel(input_file_path, sheet_name='Sheet1')

    # 定义一个函数来检查字符串是否全部为英文符号或存在标点符号
    def contains_special_chars(s):
        if not isinstance(s, str):
            return False
        # 检查字符串是否全部为英文符号
        if re.match(r'^[a-zA-Z0-9\s]+$', s):
            return True
        # 检查字符串是否存在标点符号（保留小括号）
        if re.search(r'[^\w\s()（）]', s):
            return True
        return False

    # 确保所有数据都是字符串类型
    df['customerName'] = df['customerName'].astype(str)

    # 筛选出符合条件的数据行
    df_sheet2 = df[df['customerName'].apply(contains_special_chars)]

    # 将筛选出的数据行保存到sheet2
    with pd.ExcelWriter(output_file_path, engine='openpyxl') as writer:
        df_sheet2.to_excel(writer, sheet_name='去重有英文简写有特殊字符', index=False)

    # 从sheet1中去除sheet2中的相同数据行
    df_sheet3 = df[~df.isin(df_sheet2)].dropna()

    # 将差集保存到sheet3
    with pd.ExcelWriter(output_file_path, engine='openpyxl', mode='a') as writer:
        df_sheet3.to_excel(writer, sheet_name='去重无英文简写无特殊字符', index=False)

    print("处理完成")

def get_excel_hebing_data():
    # 读取三个Excel文件
    file_paths = [r"C:\Users\64531\Desktop\主数据\02bi各个系统客商查询\02bi各个系统客商查询(数据截止到2024-11-08).xlsx",

                  r"C:\Users\64531\Desktop\主数据\02bi各个系统客商查询\02bi各个系统客商查询(数据截止到2024-11-08).xlsx"]
    dfs = [pd.read_excel(file_path, sheet_name='Sheet1') for file_path in file_paths]

    # 合并三个DataFrame
    combined_df = pd.concat(dfs, ignore_index=True)

    # 定义一个函数来合并不同的值
    def merge_unique_values(series):
        unique_values = series.unique()
        return ';'.join(unique_values) if len(unique_values) > 1 else unique_values[0]

    # 根据customerName进行分组，并合并orginSystem和organization字段
    grouped_df = combined_df.groupby('customerName').agg({
        'orginSystem': merge_unique_values,
        'organization': merge_unique_values
    }).reset_index()

    # 保存结果到新的Excel文件
    output_file_path = r'C:\Users\64531\Desktop\主数据\02bi各个系统客商查询\02BI系统客商合并且去重.xlsx'
    grouped_df.to_excel(output_file_path, sheet_name='Sheet1', index=False)

    print("处理完成，结果已保存到", output_file_path)

# 根据某个列进求每个表的差集
def get_cha_data_():
    # 读取Excel文件
    excel_a = pd.read_excel(r"C:\Users\64531\Desktop\主数据\02bi各个系统客商查询\03bi所有去重客商统计(处理版).xlsx", sheet_name='去重无英文简写无特殊字符')
    excel_b = pd.read_excel(r"C:\Users\64531\Desktop\主数据\02bi各个系统客商查询\04自有+BI系统客商合并且去重.xlsx", sheet_name='Sheet1')

    # 提取customerName列
    customer_names_a = set(excel_a['customerName'])
    customer_names_b = set(excel_b['customerName'])

    # 计算差集
    a_minus_b = customer_names_a - customer_names_b
    b_minus_a = customer_names_b - customer_names_a

    # 过滤出差集数据
    a_diff = excel_a[excel_a['customerName'].isin(a_minus_b)]
    b_diff = excel_b[excel_b['customerName'].isin(b_minus_a)]

    # 创建一个新的Excel文件并保存结果
    with pd.ExcelWriter(r"C:\Users\64531\Desktop\主数据\02bi各个系统客商查询\05差集.xlsx") as writer:
        a_diff.to_excel(writer, sheet_name='Sheet1', index=False)
        b_diff.to_excel(writer, sheet_name='Sheet2', index=False)

    print("差集已保存到 result.xlsx")

def getHistoryCus():
    # 读取 Excel 文件
    a_excel = pd.read_excel(r"C:\Users\64531\Desktop\正式天眼查客商校验\天眼查Excel批量查询客商19519.xlsx")
    b_excel = pd.read_excel(r"C:\Users\64531\Desktop\正式天眼查客商校验\1筛选1中有历史名称json的.xlsx")

    # 初始化结果列表
    result = []

    # 获取 aExcel 表中的所有资源客户名称，并存储在一个集合中
    resource_names_set = set(a_excel['资源客户名称'])

    # 定义一个函数来检查 BasicInfoJson 中 key 为 historyNames 的 value 是否包含任何一个资源客户名称
    def contains_resource_name(basic_info_json_str, resource_names_set):
        if not isinstance(basic_info_json_str, str):
            print(f"警告: BasicInfoJson 不是字符串，尝试转换: {basic_info_json_str}")
            try:
                basic_info_json_str = str(basic_info_json_str)
            except Exception as e:
                print(f"转换失败: {e}")
                return False, None

        try:
            basic_info_json = json.loads(basic_info_json_str)

            # 检查 BasicInfoJson 中 key 为 historyNames 的 value 是否包含任何一个资源客户名称
            if 'historyNames' in basic_info_json:
                history_names = basic_info_json['historyNames']
                matched_names = [name for name in resource_names_set if name in history_names]
                if matched_names:
                    return True, matched_names
        except json.JSONDecodeError:
            print(f"无法解析 JSON: {basic_info_json_str}")

        return False, None

    # 使用 apply 方法进行向量化操作
    b_excel[['contains_resource_name', '匹配的资源客户名称']] = b_excel['BasicInfoJson'].apply(
        lambda x: pd.Series(contains_resource_name(x, resource_names_set)))

    # 筛选出包含资源客户名称的行
    result_df = b_excel[b_excel['contains_resource_name']]

    # 删除临时列
    result_df = result_df.drop(columns=['contains_resource_name'])

    # 输出到新的 Excel 表
    result_df.to_excel(r"C:\Users\64531\Desktop\正式天眼查客商校验\7天眼查通过历史名称查询的客商.xlsx", index=False)

    print("匹配完成，结果已保存到 '历史名称的客商.xlsx'")

def get_json_oneKey():
    # 读取 Excel 文件
    b_excel = pd.read_excel(r"C:\Users\64531\Desktop\正式天眼查客商校验\4客商名称没有非法字符.xlsx")

    # 初始化结果列表
    result = []

    # 遍历 bExcel 表的每一行
    for index, row in b_excel.iterrows():
        basic_info_json_str = row['BasicInfoJson']

        # 将 BasicInfoJson 转换为字符串
        if not isinstance(basic_info_json_str, str):
            print(f"警告: BasicInfoJson 不是字符串，尝试转换: {basic_info_json_str}")
            try:
                basic_info_json_str = str(basic_info_json_str)
            except Exception as e:
                print(f"转换失败: {e}")
                continue

        try:
            basic_info_json = json.loads(basic_info_json_str)

            # 检查 tags 是否存在且包含 "经营异常"
            if 'tags' in basic_info_json and '经营异常' in basic_info_json['tags']:
                # 将 tags 的值复制到表格的最后一列
                row['tags'] = basic_info_json['tags']
                result.append(row.to_dict())
        except json.JSONDecodeError:
            print(f"无法解析 JSON: {basic_info_json_str}")

    # 将结果转换为 DataFrame
    result_df = pd.DataFrame(result)

    # 输出到新的 Excel 表
    result_df.to_excel(r"C:\Users\64531\Desktop\5客商名称没有非法字符提取tags.xlsx", index=False)

    print("筛选完成，结果已保存到 '筛选结果.xlsx'")


def get_yihcnagCus():
    # 读取 Excel 文件
    a_excel = pd.read_excel(r"C:\Users\64531\Desktop\正式天眼查客商校验\2BI系统中全账套全系区分单据类型的客商单量1.xlsx")
    b_excel = pd.read_excel(r"C:\Users\64531\Desktop\正式天眼查客商校验\5客商名称没有非法字符提取并提取经营异常tags的客商.xlsx")

    # 初始化结果列表
    result = []

    # 遍历 aExcel 表的每一行
    for index_a, row_a in a_excel.iterrows():
        customer_name = row_a['customerName']

        # 遍历 bExcel 表的每一行
        for index_b, row_b in b_excel.iterrows():
            name = row_b['Name']

            # 如果匹配成功，将 aExcel 的行信息添加到结果列表
            if customer_name == name:
                result.append(row_a.to_dict())
                break

    # 将结果转换为 DataFrame
    result_df = pd.DataFrame(result)

    # 输出到新的 Excel 表
    result_df.to_excel(r"C:\Users\64531\Desktop\正式天眼查客商校验\6经营异常的客商不同单据类型的单量.xlsx", index=False)

    print("匹配完成，结果已保存到 '匹配结果.xlsx'")

def get_oldCus_bill():

    # 读取 Excel 文件
    a_excel = pd.read_excel(r"C:\Users\64531\Desktop\正式天眼查客商校验\2BI系统中全账套全系统不区分单据类型的客商单量.xlsx")
    b_excel = pd.read_excel(r"C:\Users\64531\Desktop\正式天眼查客商校验\3客商名称含非法字符.xlsx")

    # 将需要匹配的列转换为字符串类型
    a_excel['customerName'] = a_excel['customerName'].astype(str)
    b_excel['含非法字符的客商名称'] = b_excel['含非法字符的客商名称'].astype(str)

    # 创建一个集合，用于快速查找
    b_names_set = set(b_excel['含非法字符的客商名称'])

    # 使用 apply 方法进行匹配
    a_excel['is_matched'] = a_excel['customerName'].apply(lambda x: any(x in name for name in b_names_set))

    # 过滤出匹配成功的行
    result_df = a_excel[a_excel['is_matched']]

    # 删除临时列
    result_df.drop(columns=['is_matched'], inplace=True)

    # 输出到新的 Excel 表
    result_df.to_excel(r'C:\Users\64531\Desktop\正式天眼查客商校验\9含非法字符的客商名称不区分单据类型的单量.xlsx', index=False)

    print("匹配完成，结果已保存到 '旧客户名称的单量.xlsx'")

 # 给定两个Excel根据A表的‘name’列和B表的‘资源客户名称’列取B表数据的差集
def get_chajiByName():

    # 读取Excel文件
    df_a = pd.read_excel(r"C:\Users\64531\Desktop\天眼查返回的17997条客商查询结果.xlsx", engine='openpyxl')
    df_b = pd.read_excel(r"C:\Users\64531\Desktop\正式天眼查客商校验\天眼查Excel批量查询客商19519.xlsx", engine='openpyxl')

    # 指定用于比较的列
    col_a = 'Name'
    col_b = '资源客户名称'

    # 计算差集
    diff_df = df_b[~df_b[col_b].isin(df_a[col_a])]

    # 将结果保存到新的Excel文件
    diff_df.to_excel(r"C:\Users\64531\Desktop\正式天眼查客商校验\测试天眼查漏查数据1.xlsx", index=False, engine='openpyxl')

    print("处理完成，结果已保存到 '天眼查漏查数据1.xlsx'")




if __name__ == '__main__':
    # match_excel_v1(r"C:\Users\64531\Desktop\ABsheet处理数据 - 原始.xlsx")
    # match_excel_v2(r"C:\Users\64531\Desktop\output_file2.xlsx")
    # match_excel_v3(r"C:\Users\64531\Desktop\output_file3.xlsx")
    # bing_search(r"C:\Users\64531\Desktop\工作簿2.xlsx",r"C:\Users\64531\Desktop\qucahji.xlsx")
    # get_excel_data()
    # get_excel_hebing_data()
    # get_cha_data_()
    # getHistoryCus()
    # get_json_oneKey()
    # get_yihcnagCus()
    get_oldCus_bill()
    # get_chajiByName()
